# 导入:
from sqlalchemy import Column, String, Integer, DateTime, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()

# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(Integer, primary_key=True)
    name = Column(String(20))

class Device(Base):
    __tablename__ = 'device'

    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    sn = Column(String(20))
    model = Column(String(20))
    position = Column(String(100))
    image = Column(String(100))
    alive = Column(Integer)

    created_time = Column(String(20))
    updated_time = Column(String(20))

class Model(Base):
    __tablename__ = 'model'

    id = Column(String(20), primary_key=True)
    name = Column(String(20))# 模板名称
    tag = Column(String(20)) # 类别：证书、记录
    header = Column(String(20)) # 表头: [1,1,1,1]

    created_time = Column(String(20))
    updated_time = Column(String(20))

class Data(Base):
    __tablename__ = 'data'

    id = Column(String(20), primary_key=True)
    key = Column(String(20))
    value = Column(String(20)) 
    created_time = Column(DateTime())

# 初始化数据库连接:
# engine = create_engine('mysql+mysqlconnector://root:password@localhost:3306/test')
engine = create_engine('sqlite:///test.db')
Base.metadata.create_all(engine) #创建表结构
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

# 测试数据
# 创建session对象:
session = DBSession()


from common.libs.Helper import generateUUID, getCurrentTime
def addTestData():
    for i in range(0, 10):
        device = Device(
            id=i, 
            name='设备%s'%i, 
            sn='%s'%generateUUID()[0:8], 
            position='位置%s'%i, 
            model='FSC%s'%i, 
            image='xxx.png', 
            created_time=getCurrentTime(), 
            updated_time=getCurrentTime()
            )
        session.add(device)
    session.commit()

# addTestData()
# session.close()


# 创建新User对象:
# new_user = User(id='5', name='Bob')
# 添加到session:
# session.add(new_user)
# 提交即保存到数据库:
# session.commit()
# 关闭session:
# session.close()